﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Configuration;
using System.IO;
using System.Web.Mvc;
using org.in2bits.MyXls;
using org.in2bits.MyOle2;
using System.Data;

using Think.Lenovo.Com.Common;
using Think.Lenovo.Com.Models;
using Think.Lenovo.Com.Models.Users;
using Think.Lenovo.Com.DataAccess;
using Think.Lenovo.Com.Controllers.Filter;

namespace Think.Lenovo.Com.Controllers
{
    /// <summary>
    /// 数据上报控制器
    /// </summary>
    public class DataReportController : Controller
    {
        /// <summary>
        /// 定义模板的视图
        /// </summary>
        /// <returns></returns>
        public ActionResult ViewCreateReport()
        {
            return View("/Views/ViewAddDataReport.cshtml");
        }

        /// <summary>
        /// 定义模板,生成excel文件
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [PowerFilter(Order = 1, PowerCode = "Z005")]
        public void CreateReport()
        {
            ViewUserModel user = (ViewUserModel)Session["User"];

            //将会添加到result集合中被序列化,记录错误信息
            LinkedList<String> errors = new LinkedList<string>();
            //将会添加到result集合中被序列化,记录Excel文件的绝对路径 
            StringBuilder excelFileName = new StringBuilder(256);

            #region //step 1. 得到用户定义的模板名称以及列名
            String templateName = Request["templateName"];
            if (String.IsNullOrEmpty(templateName))
            {
                errors.AddLast("模板名称不能为空");
            }
            String str = Request["columnName"];
            List<String> list = new List<string>();
            if (String.IsNullOrEmpty(str))
            {
                errors.AddLast("模板列最少有一列");
            }
            else
            {
                String[] columns = str.Split(',');
                foreach (String s in columns)
                {
                    list.Add(s);
                }
                if (list.Count < 1)
                {
                    errors.AddLast("模板的列数小于1");
                }
            }
            String Description = Request["Description"];
            if (String.IsNullOrEmpty(Description))
            {
                errors.AddLast(Description);
            }
            #endregion

            #region//step 2. 定义Excel文件
            if (errors.Count < 1)
            {
                try
                {
                    XlsDocument doc = new XlsDocument();

                    #region // step 3.1 定义excel文件名
                    //excel 模板文件保存目录
                    excelFileName.Append(ConfigurationManager.AppSettings["ExcelTemplateFileFolder"]);
                    if (excelFileName.ToString().Substring(excelFileName.Length - 1, 1) != @"\" && excelFileName.ToString().Substring(excelFileName.Length - 1, 1) != @"/")
                    {
                        excelFileName.Append(@"\");
                    }
                    excelFileName.Append(DateTime.Now.Year + @"\")
                        .Append(DateTime.Now.Month + @"\")
                        .Append(DateTime.Now.Day + @"\")
                        .Append(DateTime.Now.Hour + @"\");
                    if (!System.IO.Directory.Exists(excelFileName.ToString()))
                    {
                        System.IO.Directory.CreateDirectory(excelFileName.ToString());
                    }
                    excelFileName.Append(DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day + "-" + DateTime.Now.Hour + "-" + DateTime.Now.Minute + "-" + DateTime.Now.Second + "-" + DateTime.Now.Millisecond + ".xls");
                    doc.FileName = excelFileName.ToString();
                    #endregion

                    #region //3.2 定义excel的文件信息[例如:作者,公司信息]
                    //if (String.IsNullOrEmpty(user.RealName))
                    //{
                    //    user.RealName = " ";
                    //}
                    //doc.SummaryInformation.Author = user.RealName;
                    //if (String.IsNullOrEmpty(user.CompanyName))
                    //{
                    //    user.CompanyName = " ";
                    //}
                    //doc.DocumentSummaryInformation.Company = user.CompanyName;
                    #endregion

                    #region// 3.3 定义一张工作薄
                    string sheetName = templateName; //工作薄的名字定义为模板名称
                    Worksheet sheet = doc.Workbook.Worksheets.Add(sheetName);
                    Cells cs = sheet.Cells;

                    for (int i = 1; i <= list.Count; i++)
                    {
                        Cell c = cs.Add(1, i, list[i - 1].ToString());
                        c.HorizontalAlignment = HorizontalAlignments.Centered;
                        c.UseBorder = true;
                    }
                    doc.Save(true);
                    #endregion

                    #region//3.4 将该模板插入到表

                    DataReportModel model = new DataReportModel();
                    model.CreateDate = DateTime.Now;
                    model.CreateUserId = user.Id;
                    model.ModifyDate = model.CreateDate;

                    #region//定义一个报表上传目录
                    StringBuilder uploadFolder = new StringBuilder(256);
                    uploadFolder.Append(ConfigurationManager.AppSettings["CollectDataReportFolder"]);
                    if (uploadFolder.ToString().Substring(uploadFolder.Length - 1, 1) != @"\" && uploadFolder.ToString().Substring(uploadFolder.Length - 1, 1) != @"/")
                    {
                        uploadFolder.Append(@"\");
                    }
                    uploadFolder.Append(DateTime.Now.Year + @"\")
                        .Append(DateTime.Now.Month + @"\")
                        .Append(DateTime.Now.Day + @"\")
                        .Append(DateTime.Now.Hour + @"\")
                        .Append(DateTime.Now.Minute + @"\")
                        .Append(DateTime.Now.Second + @"\")
                        .Append(DateTime.Now.Millisecond + @"\");
                    if (!System.IO.Directory.Exists(uploadFolder.ToString()))
                    {
                        try
                        {
                            System.IO.Directory.CreateDirectory(uploadFolder.ToString());
                        }
                        catch
                        {
                            Exception e = new Exception("创建数据收集目录时,发生异常");
                            String logFolder = ConfigurationManager.AppSettings["LogRoot"];
                            Log.RecordLog(logFolder, e);
                        }

                    }
                    #endregion
                    model.ReportCollectFolder = uploadFolder.ToString();
                    model.ReportName = templateName;
                    model.ReportPath = excelFileName.ToString();
                    model.Description = Description;

                    DataReport dr = new DataReport();

                    long id = dr.AddDataReport(model);
                    if (id < 1)
                    {
                        errors.AddLast("添加模板失败!");
                    }
                    #endregion

                    #region //3.5 提示用户创建成功
                    Response.Write("<script type=\"text/javascript\">");
                    Response.Write("alert(\"创建模板成功!\");");
                    Response.Write("window.location=\"/DataReport/ListReportTemplates/1/10\"");
                    Response.Write("</script>");
                    #endregion
                }
                catch (Exception e)
                {
                    errors.AddLast("创建模板过程中发生异常,请查看日志文件!");
                    String logRoot = ConfigurationManager.AppSettings["LogRoot"];
                    Log.RecordLog(logRoot, e);
                    #region//提示用户创建模板失败
                    Response.Write("<script type=\"text/javascript\">");
                    Response.Write("alert(\"创建模板失败\");");
                    Response.Write("window.location.back(-1);");
                    Response.Write("</script>");
                    #endregion
                }
            }
            else
            {
                Response.Write("<script type=\"text/javascript\">");
                Response.Write("alert(\"创建模板失败\");");
                Response.Write("window.location.back(-1);");
                Response.Write("</script>");
            }
            #endregion
        }

        /// <summary>
        /// 删除报表
        /// </summary>
        /// <returns>错误 信息</returns>
        public ActionResult DeleteReport()
        {
            JsonResult jr = new JsonResult();
            Dictionary<String, Object> result = new Dictionary<string, object>();
            LinkedList<String> errors = new LinkedList<string>();
            String strId = Request["Id"];
            int id = 0;
            if (!Int32.TryParse(strId, out id))
            {
                errors.AddLast("id不是正整数");
            }
            try
            {
                DataReport dr = new DataReport();
                DataReportModel model = dr.GetDataReport(id);
                if (model == null)
                {
                    errors.AddLast("报表不存在");
                }
                else
                {
                    if (String.IsNullOrEmpty(model.ReportPath))
                    {
                        errors.AddLast("报表模板文件路径为空!");
                    }
                    else
                    {
                        #region//step 1. 从数据表中删除模板信息数据
                        int affect = dr.DeleteReport(id);
                        if (affect < 1)
                        {
                            errors.AddLast("删除模板信息失败");
                        }
                        #endregion
                        #region//step 2. 从磁盘上删除文件
                        if (System.IO.File.Exists(model.ReportPath))
                        {
                            try
                            {
                                System.IO.File.Delete(model.ReportPath);
                            }
                            catch (Exception ex)
                            {
                                String logFolder = ConfigurationManager.AppSettings["LogRoot"];
                                Log.RecordLog(logFolder, ex);
                                errors.AddLast("删除模板文件时失败!");
                            }
                        }
                        else
                        {
                            errors.AddLast("要删除的模板文件不存在!");
                        }
                        #endregion
                    }
                }
            }
            catch (Exception e)
            {
                String logFolder = ConfigurationManager.AppSettings["LogRoot"];
                Log.RecordLog(logFolder, e);
            }
            result.Add("errors", errors);
            jr.Data = result;
            return jr;
        }

        /// <summary>
        /// excel模板的列表Action
        /// </summary>
        /// <returns></returns>
        public ActionResult ListReportTemplates(int pageIndex, int pageSize)
        {
            try
            {
                DataReport dr = new DataReport();
                #region //step 1. 得到列表
                LinkedList<DataReportModel> list = new LinkedList<DataReportModel>();
                list = dr.GetDataReports(pageIndex, pageSize, "");
                ViewBag.List = list;
                #endregion
                #region //step 2.得到分页页码
                String sql = "Select Count(Id) From tbl_datareporttemplate";
                int dataCount = dr.GetDataReportCount(sql, null);
                LinkedList<PageNumberModel> pager = PageBuilder.LinkedPage(dataCount, 10, pageIndex, pageSize, "/DataReport/ListReportTemplates/{pageIndex}/10", "{pageIndex}");
                ViewBag.Pager = pager;
                #endregion
            }
            catch (Exception e)
            {
                String logRoot = ConfigurationManager.AppSettings["LogRoot"];
                Log.RecordLog(logRoot, e);
            }
            return View("/Views/ViewListDataReportTemplate.cshtml");
        }

        /// <summary>
        /// 返回一个ViewResult 显上传页面(分页列出所有模板,供上传者指定是上传哪个模板的报表)
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public ActionResult ViewUploadDataReport(int pageIndex, int pageSize)
        {
            try
            {
                DataReport dr = new DataReport();
                #region //step 1. 得到列表
                LinkedList<DataReportModel> list = new LinkedList<DataReportModel>();
                list = dr.GetDataReports(pageIndex, pageSize, "");
                ViewBag.List = list;
                #endregion
                #region //step 2.得到分页页码
                String sql = "Select Count(Id) From tbl_datareporttemplate";
                int dataCount = dr.GetDataReportCount(sql, null);
                LinkedList<PageNumberModel> pager = PageBuilder.LinkedPage(dataCount, 10, pageIndex, pageSize, "/DataReport/ListReportTemplates/{pageIndex}/10", "{pageIndex}");
                ViewBag.Pager = pager;
                #endregion
            }
            catch (Exception e)
            {
                String logRoot = ConfigurationManager.AppSettings["LogRoot"];
                Log.RecordLog(logRoot, e);
            }
            return View("/Views/ViewUploadDataReport.cshtml");
        }

        /// <summary>
        /// 用户选择模板上传页面
        /// </summary>
        /// <param name="pageIndex">页号</param>
        /// <param name="pageSize">页尺寸</param>
        /// <param name="conditions">查询条件</param>
        /// <returns></returns>
        public ActionResult ViewAllListReport(int pageIndex, int pageSize)
        {
            DataReport dr = new DataReport();
            String conditions = String.Empty;
            #region//一页数据
            
            DataSet ds = dr.GetDataReportsBy(pageIndex, pageSize, conditions);
            ViewBag.List = ds;
            #endregion

            #region//分页页码
            int recordCount = dr.GetDataReportCountBy(conditions);
            LinkedList<PageNumberModel> pages = PageBuilder.LinkedPage(recordCount, pageSize, pageIndex, 10, "/DataReport/ViewListReport/{pageIndex}/" + pageSize, "{pageIndex}");
            ViewBag.Pager = pages;
            #endregion
            return View("/Views/ViewAllListReport.cshtml");
        }

        public ActionResult ViewListReportByUserId(int pageIndex, int pageSize)
        {
            DataReport dr = new DataReport();
            String conditions = String.Empty;
            #region//一页数据
            ViewUserModel user = (ViewUserModel)Session["User"];
            if (user.Id > 0 && user.UserRoleId != 1)
            {
                conditions = " TblB.userId=" + user.Id;
            }
            DataSet ds = dr.GetDataReportsBy(pageIndex, pageSize, conditions);
            ViewBag.List = ds;
            #endregion

            #region//分页页码
            int recordCount = dr.GetDataReportCountBy(conditions);
            LinkedList<PageNumberModel> pages = PageBuilder.LinkedPage(recordCount, pageSize, pageIndex, 10, "/DataReport/ViewListReport/{pageIndex}/" + pageSize, "{pageIndex}");
            ViewBag.Pager = pages;
            #endregion
            return View("/Views/ViewListReportByUserId.cshtml");
        }

        
        /// <summary>
        /// 上传报表
        /// </summary>
        [PowerFilter(PowerCode = "F005")]
        public void UploadDataReport()
        {
            System.Web.HttpPostedFileBase file = null;
            System.IO.Stream templateFile = null;
            System.IO.Stream customFile = null;

            try
            {
                //用户信息
                ViewUserModel user = (ViewUserModel)Session["User"];
                #region//step 1.  从客户端得到模板id,跟据模板id得到一个DataReportModel实例
                String strId = Request["templateId"];
                int id = 0;
                if (!Int32.TryParse(strId, out id))
                {
                    Response.Write("<script type=\"text/javascript\"> alert(\"请选择模板,以便我们统计数据.\");</script>");
                    return;
                }
                DataReport dr = new DataReport();
                DataReportModel model = dr.GetDataReport(id);
                #endregion

                #region //step 2. 得到用户上传的文件,验证
                //file = Request.Files.Get(0);
                file = Request.Files.Get("ExcelFile");
                if (file != null)
                {
                    //客户端上传上来的文件
                    String fileType = FileType.GetFileType(file.InputStream);
                    //文件名格式  用户id_模板id_模板名称
                    String fileName = model.ReportCollectFolder + user.Id + "_" + id + "_" + model.ReportName + fileType;
                    #region//step 2.1 保存文件
                    if (!System.IO.Directory.Exists(model.ReportCollectFolder))
                    {
                        System.IO.Directory.CreateDirectory(model.ReportCollectFolder);
                    }
                    file.SaveAs(fileName);
                    #endregion

                    #region 2.2 读取文件的列,验证此文件的类型,以及是否与模版文件的列相匹配
                    #region 2.2.1 验证文件类型
                    if (fileType != ".xls" && fileType != ".xlsx")
                    {
                        System.IO.File.Delete(fileName);
                        Response.Write("<script type=\"text/javascript\"> alert(\"上传的文件不是一个Excel文件.\");</script>");
                        return;
                    }
                    #endregion
                    #region 2.2.2验证文件列是否与模板的列相匹配
                    #region 2.2.2.1拿到客户端上传上来的excel文件的第一个sheet的第一行
                    customFile = System.IO.File.OpenRead(fileName);
                    org.in2bits.MyXls.XlsDocument customDoc = new XlsDocument(customFile);
                    Workbook customBook = customDoc.Workbook;
                    Worksheets customWss = customBook.Worksheets;
                    Worksheet customWs = customWss[0];

                    Row customRow = customWs.Rows[1];
                    #endregion
                    #region 2.2.2.2 拿到模板文件的第一个sheet的第一行
                    templateFile = System.IO.File.OpenRead(model.ReportPath);
                    org.in2bits.MyXls.XlsDocument templateDoc = new XlsDocument(templateFile);
                    Row templateRow = templateDoc.Workbook.Worksheets[0].Rows[1];
                    #endregion
                    #region 2.2.2.3 比对单元格个数,以及单元格的值
                    if (templateRow.CellCount != customRow.CellCount)
                    {
                        if (file != null)
                        {
                            file.InputStream.Close();
                        }
                        if (templateFile != null)
                        {
                            templateFile.Close();
                        }
                        if (customFile != null)
                        {
                            customFile.Close();
                        }
                        System.IO.File.Delete(fileName);
                        Response.Write("<script type=\"text/javascript\"> alert(\"上传的数据报表与模板的列数不匹配.\");</script>");
                        return;
                    }
                    else
                    {
                        for (int i = 1; i <= customRow.CellCount; i++)
                        {
                            Cell templateCell = templateRow.GetCell((ushort)i);
                            Cell customCell = customRow.GetCell((ushort)i);
                            if (!templateCell.Value.Equals(customCell.Value))
                            {
                                if (file != null)
                                {
                                    file.InputStream.Close();
                                }
                                if (customFile != null)
                                {
                                    customFile.Close();
                                }
                                if (templateFile != null)
                                {
                                    templateFile.Close();
                                }
                                System.IO.File.Delete(fileName);
                                Response.Write("<script type=\"text/javascript\"> alert(\"上传的数据报表与模板不匹配.\");</script>");
                                return;
                            }
                        }
                    }
                    #endregion
                    #endregion
                    #endregion

                    #region //记录用户上传的数据
                    UserDataReportInfoModel um = new UserDataReportInfoModel();
                    UserDataReportInfo udi = new UserDataReportInfo();
                    um = udi.Get(id, user.Id);
                    if (um.Id > 0)
                    {
                        um.TemplateId = id;
                        um.UserId = user.Id;
                        um.FilePath = fileName;
                        um.UploadDate = DateTime.Now;
                        udi.Update(um);
                    }
                    else
                    {
                        um.TemplateId = id;
                        um.UserId = user.Id;
                        um.FilePath = fileName;
                        um.UploadDate = DateTime.Now;
                        udi.Add(um);
                    }
                    #endregion
                }
                Response.Write("<script type=\"text/javascript\"> ");
                Response.Write("alert(\"上传成功.\");");
                Response.Write("history.go(-1);");
                Response.Write("</script>");
                #endregion
            }
            catch (Exception e)
            {
                String logFolder = ConfigurationManager.AppSettings["LogRoot"];
                Log.RecordLog(logFolder, e);
                //Response.Write(e.Message+"<br>");
                //Response.Write(e.Source + "<br>");
                //Response.Write(e.StackTrace + "<br>");
                Response.Write("<script type=\"text/javascript\"> alert(\"上传时发生异常.\");</script>");
            }
            finally
            {
                if (file != null)
                {
                    file.InputStream.Close();
                }
                if (customFile != null)
                {
                    customFile.Close();
                }
                if (templateFile != null)
                {
                    templateFile.Close();
                }
            }
        }

        /// <summary>
        /// 下载模板Action
        /// </summary>
        public void DownLoadDataReportTemplate(int id)
        {
            try
            {
                DataReport dr = new DataReport();
                DataReportModel model = dr.GetDataReport(id);
                Response.HeaderEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", "attachment;filename=" + model.ReportName + ".xls");
                Response.WriteFile(model.ReportPath, true);
            }
            catch (Exception e)
            {
                String logFolder = ConfigurationManager.AppSettings["LogRoot"];
                Log.RecordLog(logFolder, e);
            }
        }

        /// <summary>
        /// 下载用户上传上来的报表
        /// </summary>
        /// <param name="id"> UserDataReportModel 实例的Id属性值(tbl_userdatareportinfo  表的主键值)</param>
        public void DownLoadDataReport(int id)
        {
            try
            {
                UserDataReportInfo udi = new UserDataReportInfo();
                UserDataReportInfoModel model = udi.Get(id);
                Response.HeaderEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", "attachment;filename=" + model.UserId + ".xls");
                Response.WriteFile(model.FilePath, true);
            }
            catch (Exception e)
            {
                String logFolder = ConfigurationManager.AppSettings["LogRoot"];
                Log.RecordLog(logFolder, e);
            }
        }

        /// <summary>
        /// 查看某个模板 用户上传的报表 的情况;
        /// </summary>
        /// <param name="id">模板id</param>
        /// <returns></returns>
        public ActionResult ViewUploadReportFile(int id, int pageIndex, int pageSize)
        {
            UserDataReportInfo udi = new UserDataReportInfo();
            #region//setp 1. 得到一个列表
            LinkedList<UserDataReportInfoModel> list = udi.GetListByTemplateId(id, pageIndex, pageSize);
            ViewBag.List = list;
            #endregion

            #region//step 2.得到分页页码
            int templateCount = udi.GetCountByTemplateId(id);
            LinkedList<PageNumberModel> pager = PageBuilder.LinkedPage(templateCount, pageSize, pageIndex, 10, "/DataReport/ViewUploadReportFile/" + id + "/{pageIndex}/10", "{pageIndex}");
            ViewBag.Pager = pager;
            #endregion
            return View("/Views/ViewListDataReport.cshtml");
        }
    }
}

